package cn.dakaqi.apps.group.admin.view;

import cn.dakaqi.entities.user.Volunteer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * Copyright: Copyright (c)2016
 * Company: 志愿者打卡器(www.dakaqi.cn)
 * 类名称: ViewExcel <br>
 * 类描述: <br>
 *
 * @author: ChunYang.Zhang
 * @since: 2016/7/26 14:35
 * @version: 1.0.0
 */

/**
 * 生成excel视图，可用excel工具打开或者保存
 * 由ViewController的return new ModelAndView(viewExcel, model)生成
 */
@Component
public class ViewExcel extends AbstractExcelView
{

    public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook,
                                   HttpServletRequest request, HttpServletResponse response)
            throws Exception
    {

        String excelName = (String )model.get("fileName");
        String sheetName = (String )model.get("sheetName");
        String[] title = (String[]) model.get("title");
        // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
        //response.setContentType("APPLICATION/OCTET-STREAM");
        //response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8"));
        // 清空response
        response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=\"" + new String(excelName.getBytes("gbk"), "iso-8859-1") + "\"");
        response.setContentType("application/octet-stream;charset=UTF-8");


        // 产生Excel表头
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFCellStyle setBorder = workbook.createCellStyle();

        //设置背景色：
        setBorder.setFillForegroundColor((short) 13);// 设置背景色
        setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置边框:
        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        //设置居中:
        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        //设置字体:
        HSSFFont font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);//设置字体大小

        HSSFFont font2 = workbook.createFont();
        font2.setFontName("仿宋_GB2312");
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        font2.setFontHeightInPoints((short) 12);

        setBorder.setFont(font);//选择需要用到的字体格式
        //设置列宽:
        sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
        //设置自动换行:
        setBorder.setWrapText(true);//设置自动换行
        //合并单元格:
        //Region region1 = new Region(0, (short) 0, 0, (short) 6);
        //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        //CellRangeAddress region2 = new CellRangeAddress(1, 2, (short) 0, (short) 11);
        //但应注意两个构造方法的参数不是一样的，具体使用哪个取决于POI的不同版本。
        //sheet.addMergedRegion(region1);


        HSSFRow titleRow = sheet.createRow(0); // 第0行
        // 产生标题列
        createTitleRow(workbook, titleRow, title);
        // 填充数据
        List<Volunteer> data = (List<Volunteer>) model.get("list");
        int rowNum = 1;
        int col = 0;
        for (Volunteer volunteer:data )
        {
            HSSFRow row=sheet.createRow(rowNum++);
            int colNum=0;

            col=colNum++;
            String realName = volunteer.getRealName();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == realName ? "" : realName);

            col=colNum++;
            String mobile = volunteer.getMobile();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == mobile?"" : mobile);


            col=colNum++;
            String sex = volunteer.getSex();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == sex ? "" : sex);


            col=colNum++;
            String serviceField = volunteer.getServiceField();
            row.createCell(col).setCellStyle(setBorder);
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == serviceField?"":serviceField);


            col=colNum++;
            String job = volunteer.getJob();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == job?"":job);


            col=colNum++;
            String skill = volunteer.getSkill();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == skill?"":skill);


            col=colNum++;
            String cardType = volunteer.getCardType();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == cardType?"":cardType);


            col=colNum++;
            String cardNO = volunteer.getCardNO();
            row.createCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);
            row.createCell(col).setCellValue(null == cardNO?"":("'"+cardNO));

        }

//        // 列总和计算
//        HSSFRow row = sheet.createRow(rowNum);
//        row.createCell((short) 0).setCellValue("TOTAL:");
//        String formual = "SUM(D2:D" + rowNum + ")"; // D2到D[rowNum]单元格起(count数据)
//        row.createCell((short) 3).setCellFormula(formual);
    }


    private static void createTitleRow(HSSFWorkbook workbook,HSSFRow row,String[] title)
    {
        int length=title.length;
        HSSFCellStyle style=workbook.createCellStyle();
        HSSFFont font=workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short)14);
        font.setBoldweight((short)24);
        style.setFont(font);
        style.setFillBackgroundColor(HSSFColor.YELLOW.index);
        for(int i=0;i<length;i++)
        {
            HSSFCell cell=row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
    }
}
